import glob
import os

import xlrd

from docx import Document
from docx.enum.section import WD_ORIENTATION
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT
from docx.enum.table import WD_CELL_VERTICAL_ALIGNMENT
from docx.shared import (
    Pt,
    Cm,
    Mm,
    Inches,
)

from decorators.decorators import *
from excel_to_word.common.utils import read_config_file


def get_sheet(sheet_name):
    sheet = book.sheet_by_name(sheet_name)
    nrows = sheet.nrows  # 行数
    ncols = sheet.ncols  # 列数
    datas = []  # 存放数据
    keys = sheet.row_values(0)  # 第一行标题

    for row in range(1, nrows):
        data = {}  # 每一行数据
        for col in range(0, ncols):
            value = str(sheet.cell_value(row, col))  # 取出每一个单元格的数据
            # 替换特殊字符
            value = value.replace('<', '').replace('>', '').replace('$', '')
            data[keys[col]] = value

        # 截取第一行元素
        arrs = [value for value in data.values()]
        # 通过测试用例编号分组，用的很少
        first = arrs[0]
        # 通过二级模块分组，常用
        second = arrs[2]
        # 通过功能点名称分组，常用
        third = arrs[4]

        data['first'] = first
        data['second'] = second
        data['third'] = third

        datas.append(data)

    logger.info('excel文件内容获取成功！！')
    return datas


def generate_word_file():
    logger.info('开始生成word文档！')
    # 定义word文档对象
    doc = Document()

    # 添加横向布局的章节
    section = doc.sections[0]
    section.orientation = WD_ORIENTATION.PORTRAIT
    section.page_height = Cm(29.7)  # 设置纸张的高度
    section.page_width = Cm(21.0)  # 设置纸张的宽度
    section.left_margin = Mm(25.4)  # 设置左边距
    section.right_margin = Mm(25.4)  # 设置右边距
    section.top_margin = Mm(25.4)  # 设置上边距
    section.bottom_margin = Mm(25.4)  # 设置底部边距

    # 设置字体
    doc.styles['Normal'].font.name = '宋体'

    return doc


def add_table_to_document_up(doc, data_table):
    # 表格上段
    # 新增表格 7行4列
    doc_table = doc.add_table(rows=7, cols=4)
    doc_table.style = "Table Grid"
    doc_table.style.font.size = Pt(12)
    doc_table.style.font.name = '宋体'

    # 合并单元格 赋值
    merge_cells = [
        (0, 1, 0, 3),
        (1, 1, 1, 3),
        (2, 1, 2, 3),
        (3, 1, 3, 3),
        (4, 1, 4, 3),
        (5, 1, 5, 3)
    ]

    cell_texts = [
        ('需求编号', '暂无'),
        ('用例编号', data_table[0]['用例编号']),
        ('用例名称', f"对{data_table[0]['一级模块']}所有功能点进行用例编写"),
        ('用例说明', data_table[0]['用例描述']),
        ('测试内容', f"验证{data_table[0]['一级模块']}模块中所有功能"),
        ('前置条件', data_table[0]['前置条件'])
    ]

    for row_start, col_start, row_end, col_end in merge_cells:
        doc_table.cell(row_start, col_start).merge(doc_table.cell(row_end, col_end))

    for i, (label, text) in enumerate(cell_texts):
        doc_table.rows[i].cells[0].text = label
        doc_table.rows[i].cells[1].text = text
    return doc, doc_table, data_table


def add_table_to_document_center(doc, doc_table, data_table):
    # 表格中段
    # 设置标题
    head_cells = doc_table.rows[6].cells
    head_cells[0].text = '序号'
    head_cells[1].text = '测试步骤'
    head_cells[2].text = '预期结果'
    head_cells[3].text = '实际结果'
    # 第1 列水平居中，并设置行高，所有单元格内容垂直居中
    for i in range(0, 7):
        # 水平居中
        p = doc_table.rows[i].cells[0].paragraphs[0]
        p.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
        doc_table.rows[i].height = Cm(0.6)  # 行高
        # 垂直居中
        for j in range(0, 4):
            doc_table.rows[i].cells[j].vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER
    # 生成表格并填充内容
    row_num = 0
    for data in data_table:
        row = doc_table.add_row()
        row_cells = row.cells
        row_cells[0].text = str(row_num + 1)  # 序号，需要转换成字符串
        row_cells[1].text = data['测试步骤']
        row_cells[2].text = data['预期结果']
        row_cells[3].text = data['预期结果']
        # 水平居中
        p = row_cells[0].paragraphs[0]
        p.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
        row.height = Cm(0.6)  # 行高
        # 垂直居中
        for j in range(0, 4):
            row_cells[j].vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER
        row_num = row_num + 1
    doc.add_paragraph(text='', style=None)  # 换行
    return doc, doc_table, data_table


def add_table_to_document_down(doc_table, comment_str):
    # 表格下段
    # 循环五次新增列表用来填写结果和其他信息
    for _ in range(6):
        doc_table.add_row()
    # 单元格合并
    merge_cells = [
        (-1, 1, -1, 3),
        (-2, 1, -2, 3),
        (-3, 1, -3, 3),
        (-4, 1, -4, 3),
        (-5, 1, -5, 3),
        (-6, 1, -6, 3)
    ]

    # 单元格文本
    cell_texts = [
        (('测试责任人', ''), WD_PARAGRAPH_ALIGNMENT.CENTER),
        (('判定结果', ''), WD_PARAGRAPH_ALIGNMENT.CENTER),
        (('测试时间', ''), None),
        (('实测截图', ''), None),
        (('评判标准', '每一步实际结果与预期结果相符，判定该用例通过；否则为不通过'), None),
        (('备注', comment_str), None)
    ]

    for row_start, col_start, row_end, col_end in merge_cells:
        doc_table.rows[row_start].cells[col_start].merge(doc_table.rows[row_end].cells[col_end])

    for i, ((label, text), alignment) in enumerate(cell_texts[::-1]):
        doc_table.rows[-i - 1].cells[0].text = label
        doc_table.rows[-i - 1].cells[1].text = text
        if alignment is not None:
            p = doc_table.rows[-i - 1].cells[1].paragraphs[0]
            p.paragraph_format.alignment = alignment

    # 设置列宽
    col_width_dic = {0: 2, 1: 3, 2: 3, 3: 2}

    for col_num in range(4):
        doc_table.cell(0, col_num).width = Inches(col_width_dic[col_num])
    return doc_table


def extract_comment(data_tables):
    """传入一个列表嵌套字典,提取同个分类下的备注数据，返回备注文本"""
    comment_list = [data_table['备注'] for data_table in data_tables if data_table.get('备注')]
    comment_str = '\n'.join(str(comment) for comment in comment_list)
    return comment_str


def get_filter_config(filter_model):
    """获取过滤器配置
    first:根据测试用例编号分类，通常一条用例对应一个表格
    second:根据测试用例二级模块分类，通常一个模块对应一个表格
    third:根据测试用例功能名称分类，通常一个功能对应一个表格
    """
    filter_mapping = {
        1: "first",
        2: "second",
        3: "third"
    }

    data_filter = filter_mapping.get(filter_model)
    if data_filter is None:
        logger.error("过滤配置错误，请检查")

    return data_filter


@print_execution_time('%Y/%m/%d %H:%M:%S')
def excel_to_docx(exword_path, sheet_name, datas, filter_model):
    """生成word文件"""
    doc = generate_word_file()
    # 获取数据中的second的值并去重
    filter = get_filter_config(filter_model)
    datas_filter = [data[f'{filter}'] for data in datas]
    data_filter = []
    [data_filter.append(filter_value) for filter_value in datas_filter if not filter_value in data_filter]

    h2 = doc.add_heading(sheet_name, level=2)  # 二级标题
    run = h2.add_run('')  # 可以通过add_run来设置文字，也可以通过数组来获取
    run.font.name = '宋体'

    for filter_value in data_filter:
        h3 = doc.add_heading(filter_value, level=3)  # 插入模板名称,三级标题
        run = h3.add_run('')  # 可以通过add_run来设置文字，也可以通过数组来获取
        run.font.name = '宋体'
        run.font.size = Pt(16)
        doc.add_paragraph(text='', style=None)  # 换行

        data_table = [data for data in datas if (data[f'{filter}'] == filter_value)]
        doc, doc_table, data_table = add_table_to_document_up(doc, data_table)
        doc, doc_table, data_table = add_table_to_document_center(doc, doc_table, data_table)
        comment_str = extract_comment(data_table)
        doc_table = add_table_to_document_down(doc_table, comment_str)
        max_row = len(doc_table.rows)
        # 对所有行和列水平垂直居中
        for i in range(0, max_row):
            # 水平居中
            p = doc_table.rows[i].cells[0].paragraphs[0]
            p.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
            doc_table.rows[i].height = Cm(1.0)  # 设置行高
            # 垂直居中
            for j in range(0, 4):
                doc_table.rows[i].cells[j].vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER
    try:
        xlsx_path = os.path.join(exword_path, 'result_document', f'{sheet_name}.docx')
        doc.save(xlsx_path)
    except PermissionError:
        logger.error('文件已经生成，请检查！！')
    else:
        logger.info(f'工作表:{sheet_name}转换完成，感谢使用！！')


if __name__ == '__main__':
    exword_path = os.getcwd()

    folder_path = os.path.join(exword_path, 'original_document')
    # 使用glob模块匹配文件夹下的所有以.xlsx为后缀的文件
    xlsx_files = glob.glob(os.path.join(folder_path, '*.xlsx'))

    config_path = os.path.join(exword_path, 'config\config.ini')
    filter_model = read_config_file(config_path)
    for word_path in xlsx_files:
        book = xlrd.open_workbook(word_path)
        sheet_names = list(book.sheet_names())
        for sheet_name in sheet_names:
            excel_datas = get_sheet(sheet_name)
            if len(excel_datas) < 1:
                logger.error('Excel没有内容')
            else:
                logger.info('开始转换')
                excel_to_docx(exword_path, sheet_name, excel_datas, filter_model=filter_model)
